Deseasoning Crime

source('~/Google Drive/work_analysis/policestat/policestat_reporting/src/biweekly_reporting.R', echo=TRUE)

> library(tidyverse)

> library(RSocrata)

> library(RcppRoll)

> library(ggiteam)

> library(scales)

> library(lubridate)

> library(rgdal)

> library(leaflet)

> library(RODBC)

> library(htmltools)

> library(readxl)

> source("src/functions.R")

cannot open file 'src/functions.R': No such file or directoryError in file(filename, "r", encoding = encoding) : 
  cannot open the connection

library(tidyverse)

violent_crime <- get_violent_crime_socrata()

rolling_counts_by_district <- get_rolling_counts_by_district(violent_crime)
rolling_counts_citywide <- get_rolling_counts_citywide(violent_crime)

rolling_counts_citywide %>%
  ggplot(aes(crimedate, roll_90, color = description)) +
  geom_line()

deseasoned_homicides_shootings_historical <- rolling_counts_citywide %>%
  filter(description == "HOMICIDE + SHOOTING", 
         !is.na(roll_90),
         crimedate >= "2016-01-01",
         crimedate <= "2019-12-31") %>%
  select(crimedate, roll_90) %>%
  mutate(crimedate.year = year(crimedate)) %>% 
  group_by(crimedate.year) %>%
  mutate(year_avg = mean(roll_90)) %>%
  ungroup() %>%
  mutate(seasonal_ratio = roll_90 / year_avg,
         day_of_year = yday(crimedate)) %>%
  group_by(day_of_year) %>%
  mutate(
    seasonal_act_avg = mean(roll_90),
    seasonal_ratio_avg = mean(seasonal_ratio)) %>%
  ungroup() 


deseasoned_homicides_shootings_w_this_year <- rolling_counts_citywide %>%
  filter(description == "HOMICIDE + SHOOTING",
         year(crimedate) >= 2016) %>%
  mutate(day_of_year = yday(crimedate)) %>% 
  left_join(
    deseasoned_homicides_shootings_historical %>%
      filter(crimedate.year == 2016) %>%
      select(day_of_year,
             seasonal_ratio,
             seasonal_act_avg,
             seasonal_ratio_avg),
    by = "day_of_year"
  ) %>% 
  mutate(deseasoned_90d = roll_90 / seasonal_ratio_avg)

deseasoned_homicides_shootings_w_this_year %>%
  select(crimedate, roll_90, seasonal_act_avg, deseasoned_90d) %>%
  gather(key = type, value = total_90, -crimedate) %>%
  #filter(crimedate >= max(crimedate) - 3 * 365) %>%
  filter(type != "deseasoned_90d") %>%
  ggplot(aes(crimedate, total_90, color = type)) +
  geom_line(size = .7) +
  #facet_grid(rows = vars(type)) +
  scale_x_date(date_minor_breaks = "3 month",
               date_breaks = "1 year",
               date_labels = "%b\n%y",
               limits = c(as.Date("2016-01-01"), as.Date("2020-08-01"))
               ) +
  theme_iteam_presentations() +
  #
  #scale_color_discrete_iteam() +
  scale_color_manual(values = c(iteam.colors[2], iteam.colors[3])) +
  theme(axis.title.x = element_blank(),
        panel.grid.major.x = element_line(color="gray40"),
        panel.grid.minor.x = element_line(color = "gray70")) +
  labs(y = "90-Day Homicide + Shooting Total",
       title = "90-Day Total Homicides and Shootings",
       subtitle = "Actual and 2016-2019 Seasonal Average")

deseasoned_homicides_shootings_w_this_year %>%
  select(crimedate, roll_90, seasonal_act_avg, deseasoned_90d) %>%
  gather(key = type, value = total_90, -crimedate) %>%
  #filter(crimedate >= max(crimedate) - 3 * 365) %>%
  #filter(type != "deseasoned_90d") %>%
  ggplot(aes(crimedate, total_90, color = type)) +
  geom_line(size = .7) +
  facet_grid(rows = vars(type)) +
  scale_x_date(date_minor_breaks = "3 month",
               date_breaks = "1 year",
               date_labels = "%b\n%y",
               limits = c(as.Date("2016-01-01"), as.Date("2020-08-01"))
               ) +
  theme_iteam_presentations() +
  scale_color_discrete_iteam() +
  theme(axis.title.x = element_blank(),
        panel.grid.major.x = element_line(color="gray40"),
        panel.grid.minor.x = element_line(color = "gray70")) +
  labs(y = "90-Day Homicide + Shooting Total")+
  labs(y = "90-Day Homicide + Shooting Total",
       title = "90-Day Total Homicides and Shootings",
       subtitle = "Seasonally Adjusted, Actual, and 2016-2019 Seasonal Average")

deseasoned_homicides_shootings_historical %>%
  select(crimedate, seasonal_ratio) %>%
  #filter(crimedate >= max(crimedate) - 3 * 365) %>%
  #filter(type != "deseasoned_90d") %>%
  ggplot(aes(crimedate, seasonal_ratio)) +
  geom_line(size = .7, color = iteam.colors[3]) +
  #facet_grid(rows = vars(type), scales = "free_y") +
  scale_x_date(date_minor_breaks = "3 month",
               date_breaks = "1 year",
               date_labels = "%b\n%y",
               limits = c(as.Date("2016-01-01"), as.Date("2020-08-01"))
               ) +
  theme_iteam_presentations() +
  scale_color_discrete_iteam() +
  theme(axis.title.x = element_blank(),
        panel.grid.major.x = element_line(color="gray40"),
        panel.grid.minor.x = element_line(color = "gray70")) 

deseasoned_all_citywide <- deseason_rolling_90d_counts(rolling_counts_citywide)
deseasoned_all_by_district <- deseason_rolling_90d_counts(rolling_counts_by_district)

deseasoned_all_citywide %>%
  filter(description %in% c("AGG. ASSAULT", "RAPE", "HOMICIDE + SHOOTING", "ROBBERY (ALL)")) %>%
  ggplot(aes(crimedate)) +
  geom_rect(aes(xmin=md_stay_at_home_start, xmax=md_stay_at_home_end, ymin=0, ymax=Inf),
            fill = "gray90", alpha = 0.5) +
  geom_line(aes(y=deseasoned_90d, color = description)) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate) - 365), 
             aes(x = crimedate, y = deseasoned_90d),
             color = iteam.colors[1], size = 3) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate)), 
             aes(x = crimedate, y = deseasoned_90d),
             color = red_orange, size = 3) +
  facet_wrap(ncol = 1, ~description, scales="free_y") +
  theme_iteam_presentations() +
  scale_color_discrete_iteam() +
  theme(legend.position = "none",
        axis.title.x = element_blank(),
        panel.grid.major.x = element_line(color = "gray90")) +
  labs(title = "Seasonally Adjusted 90-Day Crime Trends") +
  labs(y = "90 Day Rolling Total")

deseasoned_all_citywide %>%
  filter(grepl("ROBBERY", description),
         description != "ROBBERY (ALL)",
         crimedate >= "2019-01-01") %>%
  ggplot(aes(crimedate)) +
  geom_rect(aes(xmin=md_stay_at_home_start, xmax=md_stay_at_home_end, ymin=0, ymax=Inf),
            fill = "gray90", alpha = 0.5) +
  geom_line(aes(y=deseasoned_90d, color = description)) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate) - 365), 
             aes(x = crimedate, y = deseasoned_90d),
             color = iteam.colors[1], size = 3) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate)), 
             aes(x = crimedate, y = roll_90),
             color = red_orange, size = 3) +
  facet_wrap(ncol = 1, ~description, scales="free_y") +
  theme_iteam_presentations() +
  theme(legend.position = "none",
        axis.title.x = element_blank(),
          panel.grid.major.x = element_line(color = "gray90")) +
  scale_color_discrete_iteam() +
    scale_x_date(date_breaks = "1 year", date_labels = format("%Y"),
               date_minor_breaks = "1 month") +
  #labs(title = "Actual 90-Day Crime Trends") +
  labs(y = "90 Day Rolling Total")

deseasoned_all_citywide %>%
  filter(grepl("ROBBERY", description),
         description != "ROBBERY (ALL)",
         crimedate >= "2019-01-01") %>%
  ggplot(aes(crimedate)) +
  geom_rect(aes(xmin=md_stay_at_home_start, xmax=md_stay_at_home_end, ymin=0, ymax=Inf),
            fill = "gray90", alpha = 0.5) +
  geom_line(aes(y=roll_90, color = description)) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate) - 365), 
             aes(x = crimedate, y = roll_90),
             color = iteam.colors[1], size = 3) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate)), 
             aes(x = crimedate, y = roll_90),
             color = red_orange, size = 3) +
  facet_wrap(ncol = 1, ~description, scales="free_y") +
  theme_iteam_presentations() +
  theme(legend.position = "none",
        axis.title.x = element_blank(),
          panel.grid.major.x = element_line(color = "gray90")) +
  scale_color_discrete_iteam() +
      scale_x_date(date_breaks = "1 year", date_labels = format("%Y"),
               date_minor_breaks = "1 month") +
  #labs(title = "Actual 90-Day Crime Trends") +
  labs(y = "90 Day Rolling Total")

deseasoned_all_citywide %>%
  filter(grepl("ROBBERY", description),
         description != "ROBBERY (ALL)",
         crimedate >= "2019-01-01") %>%
  ggplot(aes(crimedate)) +
  geom_rect(aes(xmin=md_stay_at_home_start, xmax=md_stay_at_home_end, ymin=0, ymax=Inf),
            fill = "gray90", alpha = 0.5) +
  geom_line(aes(y=roll_90, color = description),
            size = 1.) +
  #facet_wrap(ncol = 1, ~description) +
  ylim(c(0, 1200)) +
  theme_iteam_presentations() +
  scale_color_discrete_iteam() +
  theme(legend.title = element_blank(),
        legend.position = "right",
        axis.title.x = element_blank(),
        panel.grid.major.x = element_line(color = "gray70"),
        panel.grid.minor.x = element_line(color = "gray90")) +
  #labs(title = "Seasonally Adjusted 90-Day Crime Trends") +
  labs(y = "90 Day Rolling Total") +
  scale_x_date(date_breaks = "1 year", date_labels = format("%Y"),
               date_minor_breaks = "1 month")

# THIS ONE SHOULD BE THE FUNCTION
deseasoned_all_by_district %>%
  filter(#description == "HOMICIDE + SHOOTING",
         crimedate >= "2019-01-01") %>%
  ggplot(aes(crimedate)) +
    geom_rect(aes(xmin=md_stay_at_home_start, xmax=md_stay_at_home_end, ymin=0, ymax=Inf),
              fill = "gray90", alpha = 0.5) +
  geom_line(aes(y=deseasoned_90d),  color = iteam.colors[2]) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate) - 365), 
             aes(x = crimedate, y = deseasoned_90d),
             color = iteam.colors[1], size = 3) +
  geom_point(data = . %>% 
               filter(crimedate == max(crimedate)), 
             aes(x = crimedate, y = deseasoned_90d),
             color = red_orange, size = 3) +
  facet_wrap( ~description + district, scales = "free_y") +
  theme_iteam_presentations() +
  #scale_color_discrete_iteam() +
  theme(legend.position = "none",
        axis.title.x = element_blank(),
        panel.grid.major.x = element_line(color = "gray90"),
        panel.grid.minor.x = element_blank()) +
  labs(title = "Seasonally Adjusted 90-Day Crime Trends") +
  labs(y = "90 Day Rolling Total") +
  scale_x_date(date_breaks = "1 year", date_labels = format("%Y"),
               date_minor_breaks = "1 month") 
source('~/Google Drive/work_analysis/policestat/policestat_reporting/src/biweekly_reporting.R', echo=TRUE)

> library(tidyverse)

> library(RSocrata)

> library(RcppRoll)

> library(ggiteam)

> library(scales)

> library(lubridate)

> library(rgdal)

> library(leaflet)

> library(RODBC)

> library(htmltools)

> library(readxl)

> source("src/functions.R")

Error in file(filename, "r", encoding = encoding) : 
  cannot open the connection

deseasoned_all_citywide %>%
  filter(description == "HOMICIDE + SHOOTING")